package com.contract.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.contract.dao.ContractDao;
import com.contract.pojo.Markinfo;
import com.contract.pojo.Tradinfo;

public class ContractDaoImpl implements ContractDao {

	
	@Override
	public List queryExpMonth(Connection connection) {
		
		List list = new ArrayList();
		try {
	
			// 执行sql
			String sql = "select exp_month from contract where is_past=1 GROUP BY exp_month";
			PreparedStatement prepareStatement = connection.prepareStatement(sql);
			// 执行查询功能
			ResultSet rs  = prepareStatement.executeQuery();
			
			while (rs.next()) {
				String expMonth = rs.getString("exp_month");
				list.add(expMonth);
			}
			
			rs.close();		
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public void addTadInfo(Tradinfo tradinfo,Connection connection) throws SQLException {
						
			// 执行sql	
				String sql = "insert into tradinfo (ticker_symbol,last,chg_rat,presetpx,exepx,addyear,addmonth,addate) VALUES (?,?,?,?,?,?,?,?)";				
				PreparedStatement pst = connection.prepareStatement(sql); 
				pst.setString(1, tradinfo.getTickerSymbol());
				pst.setString(2, tradinfo.getLast());
				pst.setString(3, tradinfo.getChgrat());
				pst.setString(4, tradinfo.getPresetpx());
				pst.setString(5, tradinfo.getExepx());
				pst.setString(6, tradinfo.getAddyear());
				pst.setString(7, tradinfo.getAddmonth());
				pst.setTimestamp(8, tradinfo.getAddate());
				
				// 执行查询功能
				pst.executeUpdate();						
	
	}

	@Override
	public void addMarkInfo(Markinfo markinfo,Connection connection) throws SQLException {
		
				
				String sql = "insert into markinfo (`code`,`name`,`last`,`change`,`chg_rate`,`amp_rate`,`volume`,`amount`,addyear,addmonth,addate) VALUES (?,?,?,?,?,?,?,?,?,?,?)";				
				PreparedStatement pst= connection.prepareStatement(sql);
				pst.setString(1, markinfo.getCode());
				pst.setString(2, markinfo.getName());
				pst.setString(3, markinfo.getLast());
				pst.setString(4, markinfo.getChange());
				pst.setString(5, markinfo.getChgrate());
				pst.setString(6, markinfo.getAmprate());
				pst.setString(7, markinfo.getVolume());
				pst.setString(8, markinfo.getAmount());
				pst.setString(9, markinfo.getAddyear());
				pst.setString(10, markinfo.getAddmonth());
				pst.setTimestamp(11,markinfo.getAddate());
				// 执行查询功能
				pst.executeUpdate();

		
	}

	@Override
	public void dropTradInfo(Connection connection) throws SQLException {
		
		// 执行sql	
			String sql = "delete from tradinfo";				
			PreparedStatement pst = connection.prepareStatement(sql); 
			// 执行查询功能
			pst.executeUpdate();
							
		
	}

	@Override
	public void dropMarkInfo(Connection connection) throws SQLException {
			
			// 执行sql	
				String sql = "delete from markinfo";				
				PreparedStatement pst = connection.prepareStatement(sql); 
				// 执行查询功能
				pst.executeUpdate();
							
		
	}

	@Override
	public void addTadInfoAll(Tradinfo tradinfo, Connection connection) throws SQLException {
		
		// 执行sql	
		String sql = "insert into tradinfoall (ticker_symbol,last,chg_rat,presetpx,exepx,addyear,addmonth,addate) VALUES (?,?,?,?,?,?,?,?)";				
		PreparedStatement pst = connection.prepareStatement(sql); 
		pst.setString(1, tradinfo.getTickerSymbol());
		pst.setString(2, tradinfo.getLast());
		pst.setString(3, tradinfo.getChgrat());
		pst.setString(4, tradinfo.getPresetpx());
		pst.setString(5, tradinfo.getExepx());
		pst.setString(6, tradinfo.getAddyear());
		pst.setString(7, tradinfo.getAddmonth());
		pst.setTimestamp(8, tradinfo.getAddate());
		// 执行查询功能
		pst.executeUpdate();	
		
	}

}
